﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.ServiceModel.Activation;
using System.Web;
using System.Security.Principal;
using System.Threading;
using System.Collections;
using System.Management;
using System.Data;
namespace CMD_WEB.BAL
{
    public class GrdData
    {
         //lambda expressions
        public static List<JSONOBJ.GroupData> GetGroup(int idRol)
        {
            DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_GETGROUPBYIDROL'" + idRol.ToString() + "'");
            if (dt.Rows.Count==0)
            {
                return null;
            }
            else
            {
                List<JSONOBJ.GroupData> list = dt.AsEnumerable().Select(
                    row => new JSONOBJ.GroupData
                    {
                        IdSecGroup = row.Field<int?>(4).GetValueOrDefault(),
                        DscSecGroup = String.IsNullOrEmpty(row.Field<string>(5))
                        ? ""
                        : row.Field<string>(5),
                    }
                    ).ToList();
                return list;
            }
        }
        public static List<JSONOBJ.UserData> GetUser()
        {
            //ejecuto la consulta al store procedure
            DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_GETUSER");
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                //convierto la lista a datatable
                List<JSONOBJ.UserData> list = dt.AsEnumerable().Select(
                    row => new JSONOBJ.UserData
                    {
                        // assuming column 0's type is Nullable<long>
                        IdUser = row.Field<int?>(0).GetValueOrDefault(),
                        Name = String.IsNullOrEmpty(row.Field<string>(1))
                            ? ""
                            : row.Field<string>(1),
                        Mail = String.IsNullOrEmpty(row.Field<string>(2))
                            ? ""
                            : row.Field<string>(2),
                        DateCreated = String.IsNullOrEmpty(row.Field<string>(3))
                            ? ""
                            : row.Field<string>(3),
                        DateModified = String.IsNullOrEmpty(row.Field<string>(4))
                            ? ""
                            : row.Field<string>(4),
                    }
                ).ToList();

                return list;
            }
        }
        public static List<JSONOBJ.Rol>  GetRol()
        {
            DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_GETROL1");
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                List<JSONOBJ.Rol> list = dt.AsEnumerable().Select(
                    row => new JSONOBJ.Rol
                    {
                        idRol = row.Field<int?>(0).GetValueOrDefault(),
                        dscRol = String.IsNullOrEmpty(row.Field<string>(1))
                        ? ""
                        : row.Field<string>(1),
                        prioridad = row.Field<int?>(2).GetValueOrDefault(),
                    }
                    ).ToList();
                return list;
            }
        }
        public static CMD_WEB.BAL.JSONOBJ.InsertRol GetInsertRol(int idRol,string dscRol, int prioridad)
        {
            CMD_WEB.BAL.JSONOBJ.InsertRol insertRol;

            DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_UPSERTROL1'" + idRol.ToString() + "','" + dscRol + "','" + prioridad.ToString() + "'");
             insertRol = new JSONOBJ.InsertRol(idRol,dscRol, prioridad);
             return insertRol;
        }
        public static CMD_WEB.BAL.JSONOBJ.InsertGroupData GetInsertGroup(int idRol,int idSecGroup,string dscRol, Int32 prioridad,string dscSecGroup)
        {
            CMD_WEB.BAL.JSONOBJ.InsertGroupData insertGroup;
            DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_UPSERTGROUP'" + idRol.ToString() + "','" + idSecGroup.ToString() + "','" + dscRol + "','" + prioridad.ToString() + "','" + dscSecGroup + "'");
            insertGroup = new JSONOBJ.InsertGroupData(idRol,idSecGroup,dscRol,prioridad,dscSecGroup);
            return insertGroup;
        }  
        public static JSONOBJ.InsertUserData GetInsertUser(int idUser,string dscUserName, string dscUserMail)
        {
            CMD_WEB.BAL.JSONOBJ.InsertUserData insertUser;
            DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_UPSERTUSER'" + idUser.ToString() + "','" + dscUserName + "','" + dscUserMail + "'");
            insertUser = new JSONOBJ.InsertUserData(idUser,dscUserName,dscUserMail);
            return insertUser;
        }
        public static JSONOBJ.DeleteRol GetDeleteRol(int idRol)
        {
            CMD_WEB.BAL.JSONOBJ.DeleteRol deleteRol;
            DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_DELETEROL1'" + idRol + "'");
            deleteRol = new JSONOBJ.DeleteRol(idRol);
            return deleteRol;
        }
        public static JSONOBJ.DeleteUser GetDeleteUser(int idUser)
        {
            CMD_WEB.BAL.JSONOBJ.DeleteUser deleteUser;
            DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_DELETEUSER'" + idUser + "'");
            deleteUser = new JSONOBJ.DeleteUser(idUser);
            return deleteUser;
        }
        public static JSONOBJ.DeleteGroup GetDeleteGroup(int idRol, int idSecGroup)
        {
            CMD_WEB.BAL.JSONOBJ.DeleteGroup deleteGroup;
            DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_DELETEGROUP'" + idRol + "','" + idSecGroup + "'");
            deleteGroup = new JSONOBJ.DeleteGroup(idRol,idSecGroup);
            return deleteGroup;
        }
        //public CMD_WEB.BAL.JSONOBJ.InsertRol GetInsertRol2(CMD_WEB.BAL.JSONOBJ.InsertRol data)
        //{
        //    CMD_WEB.BAL.JSONOBJ.InsertRol insertRol;

        //    DataTable dt = CMD_WEB.DAL.SrvDB.ExecuteQuery("exec SP_CMD_UPSERTROL'" + data + "'");
        //    insertRol = new JSONOBJ.InsertRol();
        //    return insertRol;
        //}
    }
}
